/*!
 * Project's name: Ziemianie
 * Supervisor: mgr Tomasz Kornuta, Warsaw University of Technology
 * File's autors: Michal Plutecki, G1ISI, Warsaw University of Technology
 * Date: 3-12-2008
 * Description:
 * 	SCZR Coursework
 * 	Team: Jakub Rosiak, Michal Lisiecki, Michal Plutecki
 * 	Warsaw University of Technology
 * */

#include "DBConnection.h"

//the one and only instance of DBConnection class to operate on DB 
DBConnection * DBConnection::dbConnection_ = NULL;

/*!
 * \brief
 * DBConnection constructor. Should not be used.
 *
 * \see
 * DBConnection::DBConnection(std::string db_file)
 */
DBConnection::DBConnection() { }

DBConnection::~DBConnection() { 
	this->disconnect(); 
}

/*!
 * \brief
 * DBConnection constructor. Specifies a database name.
 * 
 * \param db_file
 * Database name to connect and operate on.
 *
 * \see
 * DBKlewki
 */
DBConnection::DBConnection(std::string db_file) {
	db_file_ = db_file;
	this->connect();
}

/*!
 * \brief
 * Gets the instance of DBConnection class. Implemented as Singleton template.
 * 
 * \returns
 * The instance od DBConnection.
 *
 * \see
 * DBKlewki
 */
 DBConnection * DBConnection::GetInstance() {
	 if (dbConnection_ == NULL) {
		 dbConnection_ = new DBConnection("DBKlewki.db");
	 }
	 return dbConnection_;
}

/*!
 * \brief
 * Connects to the specified by db_file database.
 *
 * \see
 * DBKlewki
 */
void DBConnection::connect() {
	//open connection
	db_ptr_ = new DBKlewki(db_file_);
}

/*!
 * \brief
 * Disconnects from the specified database.
 *
 * \see
 * DBKlewki
 */
void DBConnection::disconnect() {
	//close connection
	delete db_ptr_;
}

/*!
 * \brief
 * Checks database connection.
 * 
 * \returns
 * Boolean value: true - if connection established, false - in other case.
 *
 * \see
 * DBKlewki
 */
bool DBConnection::checkConnection() {
	//test connection using simple SELECT statement
	std::string selectStatement = "SELECT * FROM OBJECT;";
	
	//execute simple statement
	bool isOK = db_ptr_->exe(selectStatement);
	
	//check connection status
	if(!isOK)
		return false;
	else
		return true;
}

/*!
 * \brief
 * Prints DB.
 * 
 * \returns
 * Boolean value: true - if printed correctly, false - in other case.
 *
 * \see
 * DBKlewki
 */
bool DBConnection::printDB() {
	//test connection using simple SELECT statement
	std::string selectStatement = "SELECT * FROM OBJECT;";
	
	//execute simple SELECT statement
	bool isOK = db_ptr_->exe(selectStatement);
	
	//something wrong
	if(!isOK)
		return false;

	std::vector<std::string> v;
	
	//show OBJECT table to verify if connected correctly
	if(db_ptr_->vdata.size() > 0) {
		for(unsigned int i = 0; i < db_ptr_->vdata.size(); ++i) {
			if(i % 3 == 0)
				std::cout << std::endl;
			std::cout << db_ptr_->vdata[i] << "\t\t";
		}
		std::cout << std::endl << std::endl;
	}
	else
		std::cout << "no data in the OBJECT table" << std::endl;
	
	//all good
	return true;
}

/*!
 * \brief
 * Clears the whole database.
 * 
 * \returns
 * Boolean value: true - if database cleared, false - in other case.
 *
 * \see
 * DBKlewki
 */
bool DBConnection::clearDB() {
	std::string deleteStatement = 
		"DELETE FROM OBJECT;"
		"DELETE FROM RELATIONSHIP;";
	
	//execute DELETE statement
	bool isOK = db_ptr_->exe(deleteStatement);
		
	//something wrong
	if(!isOK)
		return false;
	
	//all good
	return true;
}

/*!
 * \brief
 * Initializes the database.
 * 
 * \returns
 * Boolean value: true - if succeeded, false - in other case.
 *
 * \see
 * DBKlewki
 */
bool DBConnection::initializeDB() {
	std::string insertStatement = 
		"INSERT INTO OBJECT VALUES (1, 0, 'quarry');"
		"INSERT INTO OBJECT VALUES (2, 0, 'iron ore mine');"
		"INSERT INTO OBJECT VALUES (3, 0, 'coalmine');"
		"INSERT INTO OBJECT VALUES (4, 0, 'steelworks');"
		"INSERT INTO OBJECT VALUES (5, 0, 'wind power station');"
		"INSERT INTO OBJECT VALUES (6, 0, 'power plant');"
		"INSERT INTO OBJECT VALUES (7, 0, 'water pump');"
		"INSERT INTO OBJECT VALUES (8, 0, 'sawmill');"
		"INSERT INTO OBJECT VALUES (9, 0, 'foresters lodge');"
		"INSERT INTO OBJECT VALUES (10, 0, 'chicken farm');"
		"INSERT INTO OBJECT VALUES (11, 1, 'villa');"
		"INSERT INTO OBJECT VALUES (12, 100, 'stone');"
		"INSERT INTO OBJECT VALUES (13, 0, 'electric energy');"
		"INSERT INTO OBJECT VALUES (14, 100, 'iron');"
		"INSERT INTO OBJECT VALUES (15, 100, 'coal');"
		"INSERT INTO OBJECT VALUES (16, 0, 'steel');"
		"INSERT INTO OBJECT VALUES (17, 100, 'cold water');"
		"INSERT INTO OBJECT VALUES (18, 0, 'hot water');"
		"INSERT INTO OBJECT VALUES (19, 100, 'wood');"
		"INSERT INTO OBJECT VALUES (20, 100, 'food');"
		"INSERT INTO OBJECT VALUES (21, 5, 'people');";
	
	//execute INSERT statement
	bool isOK = db_ptr_->exe(insertStatement);
	
	//something wrong
	if(!isOK)
		return false;
	
	//all good
	return true;
}

Response * DBConnection::takeFromDB(Message * msg) {
	//int to string convertion
	std::stringstream out1;
	std::stringstream out2;
	std::stringstream out3;
	out1 << msg->object1;
	out2 << msg->object2;
	out3 << msg->param1;
	
	//to return
	Response * resp = new Response();

	//SQL statement to execute
	std::string sqlStatement;

	//begin transaction and check quantity for object or relation
	sqlStatement = "BEGIN TRANSACTION; ";
	if(msg->object2 == 0) {
			sqlStatement += "SELECT QUANTITY FROM OBJECT WHERE TYPE = " + out1.str() + "; ";
	}
	else {
		if(msg->object1 < msg->object2)
			sqlStatement += "SELECT QUANTITY FROM RELATIONSHIP WHERE OBJECT_1 = " + out1.str() + " AND OBJECT_2 = " + out2.str() + "; ";
		else
			sqlStatement += "SELECT QUANTITY FROM RELATIONSHIP WHERE OBJECT_1 = " + out2.str() + " AND OBJECT_2 = " + out1.str() + "; ";
	}
	
	//execute SELECT statement
	bool isOK = db_ptr_->exe(sqlStatement);

	//something wrong
	if(!isOK) {
		db_ptr_->exe("COMMIT;");
		resp->status = Response::DB_EXECUTION_ERROR;
		return resp;
	}
	
	//all good, check if result set is empty
	if(db_ptr_->vdata.empty()) {
		db_ptr_->exe("COMMIT;");
		resp->status = Response::NO_DATA_RETURNED;
		return resp;
	}
	
	int r;
	std::istringstream ss(db_ptr_->vdata[0]);
	ss >> r;

	if(r < msg->param1) {
		db_ptr_->exe("COMMIT;");
		resp->status = Response::NOT_ENOUGH_RESOURCES;
		return resp;
	}
	else {
		//create SQL update statement using Message structure
		if(msg->object2 == 0)
			sqlStatement = "UPDATE OBJECT SET QUANTITY = QUANTITY - " + out3.str() + " WHERE TYPE = " + out1.str() + "; COMMIT;";
		else
			if(msg->object1 < msg->object2) {
				sqlStatement = "UPDATE RELATIONSHIP SET QUANTITY = QUANTITY - " + out3.str() + " WHERE OBJECT_1 = " + out1.str() + " AND OBJECT_2 = " + out2.str() + "; COMMIT;";
			}
			else {
				sqlStatement = "UPDATE RELATIONSHIP SET QUANTITY = QUANTITY - " + out3.str() + " WHERE OBJECT_1 = " + out2.str() + " AND OBJECT_2 = " + out1.str() + "; COMMIT;";
			}

		//execute UPDATE statement
		bool isOK = db_ptr_->exe(sqlStatement);
		
		//something wrong
		if(!isOK) {
			resp->status = Response::DB_EXECUTION_ERROR;
			return resp;
		}

		resp->status = Response::SUCCESS;
		return resp;
	}
}

Response * DBConnection::addToDB(Message * msg) {
	//int to string convertion
	std::stringstream out1;
	std::stringstream out2;
	std::stringstream out3;
	out1 << msg->object1;
	out2 << msg->object2;
	out3 << msg->param1;
	
	//to return
	Response * resp = new Response();
	
	//SQL statement to execute
	std::string sqlStatement;
	
	//create SQL statement using Message structure
	if(msg->object2 == 0)
		sqlStatement = "UPDATE OBJECT SET QUANTITY = QUANTITY + " + out3.str() + " WHERE TYPE = " + out1.str() + ";";
	else
		if(msg->object1 < msg->object2) {
			sqlStatement = "UPDATE RELATIONSHIP SET QUANTITY = QUANTITY + " + out3.str() + " WHERE OBJECT_1 = " + out1.str() + " AND OBJECT_2 = " + out2.str() + ";";
		}
		else {
			sqlStatement = "UPDATE RELATIONSHIP SET QUANTITY = QUANTITY + " + out3.str() + " WHERE OBJECT_1 = " + out2.str() + " AND OBJECT_2 = " + out1.str() + ";";
		}
	
	//execute UPDATE statement
	bool isOK = db_ptr_->exe(sqlStatement);
	
	//something wrong
	if(!isOK) {	
		resp->status = Response::DB_EXECUTION_ERROR;
		return resp;
	}
	else {
		resp->status = Response::SUCCESS;
		return resp;
	}
}
Response * DBConnection::getInfoFromDB(Message * msg) {
	//int to string convertion
	std::stringstream out1;
	std::stringstream out2;
	out1 << msg->object1;
	out2 << msg->object2;
	
	//to return
	Response * resp = new Response();
	
	//SQL statement to execute
	std::string sqlStatement;
	
	//create SQL statement using Message structure
	if(msg->object2 == 0)
		sqlStatement = "SELECT QUANTITY FROM OBJECT WHERE TYPE = " + out1.str() + ";";
	else {
		if(msg->object1 < msg->object2)
			sqlStatement = "SELECT QUANTITY FROM RELATIONSHIP WHERE OBJECT_1 = " + out1.str() + " AND OBJECT_2 = " + out2.str() + ";";
		else
			sqlStatement = "SELECT QUANTITY FROM RELATIONSHIP WHERE OBJECT_1 = " + out2.str() + " AND OBJECT_2 = " + out1.str() + ";";
	}
	
	//execute INSERT statement
	bool isOK = db_ptr_->exe(sqlStatement);
	
	//something wrong
	if(!isOK) {
		resp->status = Response::DB_EXECUTION_ERROR;
		return resp;
	}
	
	//all good, check if result set is empty
	if(db_ptr_->vdata.empty()) {
		resp->status = Response::NO_DATA_RETURNED;
		return resp;
	}
	else {
		//convert result string to int and return it
		int result;
		std::istringstream ss(db_ptr_->vdata[0]);
		ss >> result;
		
		resp->response = result;
		return resp;
	}
}
Response * DBConnection::setRelationInDB(Message * msg) {
	//int to string convertion
	std::stringstream out1;
	std::stringstream out2;
	std::stringstream out3;
	out1 << msg->object1;
	out2 << msg->object2;
	out3 << msg->param1;
	
	//to return
	Response * resp = new Response();
	
	std::string sqlStatement;
	
	//create insert statement to execute
	if(msg->object1 > msg->object2)
		sqlStatement = "INSERT INTO RELATIONSHIP VALUES("+out2.str()+","+out1.str()+","+out3.str()+");";
	else if(msg->object1 < msg->object2)
		sqlStatement = "INSERT INTO RELATIONSHIP VALUES("+out1.str()+","+out2.str()+","+out3.str()+");";
	else {
		resp->status = Response::FORBIDEN_RELATION;
		return resp;
	}

	//execute prepared statement
	bool isOK = db_ptr_->exe(sqlStatement);
	
	//set response status
	if(!isOK)
		resp->status = Response::DB_EXECUTION_ERROR;
	else
		resp->status = Response::SUCCESS;
	
	return resp;
}

Response * DBConnection::dropRelationInDB(Message * msg) {
	//int to string convertion
	std::stringstream out1;
	std::stringstream out2;
	out1 << msg->object1;
	out2 << msg->object2;
	
	//to return
	Response * resp = new Response();
	
	std::string sqlStatement;
	
	//create delete statemant to execute
	if(msg->object1 > msg->object2)
		sqlStatement = "DELETE FROM RELATIONSHIP WHERE OBJECT_1 = "+out2.str()+" AND OBJECT_2 = "+out1.str()+";";
	else if(msg->object1 < msg->object2)
		sqlStatement = "DELETE FROM RELATIONSHIP WHERE OBJECT_1 = "+out1.str()+" AND OBJECT_2 = "+out2.str()+";";
	else {
		resp->status = Response::FORBIDEN_RELATION;
		return resp;
	}
	
	//execute prepared statement
	bool isOK = db_ptr_->exe(sqlStatement);
	
	//set response status
	if(!isOK)
		resp->status = Response::DB_EXECUTION_ERROR;
	else
		resp->status = Response::SUCCESS;
	
	return resp;
}
